import pymysql

# 封装
class DataBase:

    host = 'localhost'
    user = 'root'
    passwd = 'root'
    database = 'runoob_db'
    db = ''
    cursor = ''

    # 构造函数，传参数则使用的传递参数的数据库，不传参数则使用默认数据库
    def __init__(self, host='localhost', user='root', passwd='root', database='runoob_db'):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.database = database
        self.get_db()

    # 连接数据库
    def get_db(self):
        try:
            self.db = pymysql.connect(
                host=self.host,
                user=self.user,
                passwd=self.passwd,
                database=self.database
            )
            self.get_cursor()
        except Exception as e:
            print('初始化数据库失败，请检查数据库连接！' + e)

    # 获取数据库游标
    def get_cursor(self):
        self.cursor = self.db.cursor()
        return self.cursor

    # 关闭连接
    def close(self):
        self.db.close()

    # 执行sql语句。例如创建表
    def execute(self, sql):
        self.cursor.execute(sql)
        self.db.commit()

    # 批量插入修改
    def insert_batch(self, sql, values):
        self.cursor.executemany(sql, values)
        return self.cursor.rowcount

    # 增删改 sql
    def insert_or_delete_or_update(self, sql):
        self.cursor.execute(sql)
        self.db.commit()
        return self.cursor.rowcount

    # 查询单条
    def select_one(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchone()

    # 查询多条
    def select_all(self, sql):
        self.cursor.execute(sql)
        return self.cursor.fetchall()

# get db
db = DataBase()
# 查询数据库版本
version = db.select_one("SELECT VERSION(), SYSDATE()")
print(version)
# print("Database version is :%s" %version)
# 查询所有的employee
employee = db.select_all("SELECT * FROM EMPLOYEE")
print(employee)
for row in employee:
    fname = row[0]
    lname = row[1]
    age = row[2]
    sex = row[3]
    income = row[4]
    # 打印结果
    print("fname=%s,lname=%s,age=%s,sex=%s,income=%s" % \
          (fname, lname, age, sex, income))
db.close()
# 创建数据库
